/**
 * 
 */
package com.juliashine.db.zzy;

//import org.springframework.jdbc.core.JdbcTemplate;
//import org.springframework.jdbc.datasource.SingleConnectionDataSource;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import com.juliashine.common.util.DBUtil;
import com.juliashine.db.spring.JDBCTemplate;
import com.juliashine.db.spring.RowMapper;



/**
 * @author Juliashine@gmail.com 2011-4-28
 *
 */
public class Test {

	DBCfg dbcfg = new DBCfg();
	
	class UriType{
		String uri_type_name;
		String uri_type_desc;
	}
	
	void testJdbcTemplate() throws Exception{
		String sql = "select uri_type_name,uri_type_desc from wa_dict_uritype";
		
		DataSource ds = new SimpleDataSource(dbcfg.getDs("rc_75"));
		
		JDBCTemplate jdbc = new JDBCTemplate(ds);
		long begin = System.currentTimeMillis();
		List<UriType> list = jdbc.query(sql, new RowMapper<UriType>(){
			@Override
			public UriType mapRow(ResultSet rs, int rowNum) throws SQLException {
				UriType uriType = new UriType();
				uriType.uri_type_name = rs.getString(1);
				uriType.uri_type_desc = rs.getString(2);
				return uriType;
			}	
		});
		long end = System.currentTimeMillis();
		System.out.println((end -begin)+" ms");
//		System.out.println(list.size());
//		for(UriType ut : list){
//			System.out.println(ut.uri_type_name + "\t" + ut.uri_type_desc);
//		}
	}
	
	void testJdbc(){
		String sql = "select uri_type_name,uri_type_desc from wa_dict_uritype";
		long begin = System.currentTimeMillis();
		Connection conn = null;
		try {
//			conn = DBUtil.getConnection("172.16.0.75", "test_litb_bi", null, "rp_dbadmin", "2906light");
			conn = new SimpleDataSource(dbcfg.getDs("rc_75")).getConnection();
			Statement st;
			st = conn.createStatement();
			ResultSet rs = st.executeQuery(sql);
			List<UriType> list = new ArrayList<UriType>();
			while(rs.next()){
				UriType ut = new UriType();
				ut.uri_type_name = rs.getString(1);
				ut.uri_type_desc = rs.getString(2);
				list.add(ut);
			}
			long end = System.currentTimeMillis();
			System.out.println((end -begin)+" ms");
//			System.out.println(list.size());
//			for(UriType ut : list){
//				System.out.println(ut.uri_type_name + "\t" + ut.uri_type_desc);
//			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.closeConn(conn);
		}
		
	}
	
	
	void testSpringJdbc(){
		
//		SingleConnectionDataSource ds = new SingleConnectionDataSource();
//		ds.setDriverClassName("");
//		ds.setUrl("");
//		ds.setUsername("");
//		ds.setPassword("");
//		JdbcTemplate jdbcTemplate = new JdbcTemplate();
//		jdbcTemplate.setDataSource(ds);
	}
	
	
	void store2MSSQLServer(){
		String sql = "insert into bi_wa_test (name) values(?)";
		Connection conn = null;
		
		long begin = System.currentTimeMillis();
		try {
//			conn = DBUtil.getMSSQLServerConnection("172.16.0.63", "bi_wa", "biwa", "123qwe");
			conn = new SimpleDataSource(dbcfg.getDs("bi_08")).getConnection();
			conn.setAutoCommit(false);
			PreparedStatement ps = conn.prepareStatement(sql);
			for(int i=0;i<26000;i++){
				ps.setString(1, "zzy"+i);
				ps.addBatch();
				if(i%10000 == 0){
					ps.executeBatch();
					ps.clearBatch();
					conn.commit();
					System.out.println(conn.getAutoCommit());
				}
			}
			ps.executeBatch();
			ps.clearBatch();
			conn.commit();
			long end = System.currentTimeMillis();
			System.out.println((end-begin)+" ms");
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.closeConn(conn);	
		} 
	}
	
	public static void main(String[] args) throws Exception {
		Test t = new Test();
		t.testJdbcTemplate();
//		t.testJdbc();
//		t.store2MSSQLServer();
		
	}

}
